﻿using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Web.Security;
using System.Configuration.Provider;
using Npgsql;

namespace AllMyMoney.MembershipProviders
{
    internal sealed class PostgresMembershipProvider:MembershipProvider
    {
        public PostgresMembershipProvider()
        {
            // Makeing defaults
            _applicationName = "AllMyMoney";
            _enablePasswordReset = false;
            _enablePasswordRetreival = false;
            _requiresQuestionAndAnswer = false;
            _requiresUniqueEmail = true;
            _maxInvalidPasswordAttempts = 3;
            _passwordAttemptWindow = 10;
            _passwordFormat = MembershipPasswordFormat.Hashed;
            _minRequiredNonAlphanumericCharacters = 0;
            _minRequiredPasswordLength = 3;
            _passwordStrengthRegularExpression = "^.*$";

            _databaseFactoryName = "Npgsql";
            _databaseConnectionString = "Server = localhost;";
            _databaseFactory = null;
            _passwordHashKey = HexToByte("FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF");
        }

        // All attributes are going here
        public override string ApplicationName
        {
            get { return _applicationName; }
            set { _applicationName = value; }
        }

        public override bool EnablePasswordReset
        {
            get { return _enablePasswordReset; }
        }

        public override bool EnablePasswordRetrieval
        {
            get { return _enablePasswordRetreival; }
        }

        public override bool RequiresQuestionAndAnswer
        {
            get { return _requiresQuestionAndAnswer; }
        }

        public override bool RequiresUniqueEmail
        {
            get { return _requiresUniqueEmail; }
        }

        public override int MaxInvalidPasswordAttempts
        {
            get { return _maxInvalidPasswordAttempts; }
        }

        public override int PasswordAttemptWindow
        {
            get { return _passwordAttemptWindow; }
        }

        public override MembershipPasswordFormat PasswordFormat
        {
            get { return _passwordFormat; }
        }

        public override int MinRequiredNonAlphanumericCharacters
        {
            get { return _minRequiredNonAlphanumericCharacters; }
        }

        public override int MinRequiredPasswordLength
        {
            get { return _minRequiredPasswordLength; }
        }

        public override string PasswordStrengthRegularExpression
        {
            get { return _passwordStrengthRegularExpression; }
        }

        //
        // System.Web.Security.MembershipProvider methods.
        //
        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            base.Initialize(name, config);

            // Database config
            _databaseFactoryName = config["databaseFactory"];
            _databaseConnectionString = config["databaseConnectionString"];

            if (_databaseFactoryName == null)
                throw new ProviderException ("Failed to locate a databaseFactory parameter in config");

            if (_databaseConnectionString == null)
                throw new ProviderException("Failed to locate a databaseConnectionString parameter in config");

            _databaseFactory = DbProviderFactories.GetFactory(_databaseFactoryName);

            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            // Load config from database
            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "SELECT * FROM config";
                command.CommandType = CommandType.Text;

                using (IDataReader reader = command.ExecuteReader ()) while (reader.Read())
                {
                    string paramName = reader.GetString(0);
                    string paramValue = reader.GetString(1);

                    if (paramName.CompareTo("applicationName") == 0)
                        _applicationName = paramValue;
                    if (paramName.CompareTo("enablePasswordReset") == 0)
                        _enablePasswordReset = (paramValue.Equals("true"));
                    else if (paramName.CompareTo("enablePasswordRetreival") == 0)
                        _enablePasswordRetreival = (paramValue.Equals("true"));
                    else if (paramName.CompareTo("requiresQuestionAndAnswer") == 0)
                        _requiresQuestionAndAnswer = (paramValue.Equals("true"));
                    else if (paramName.CompareTo("requiresUniqueEmail") == 0)
                        _requiresUniqueEmail = (paramValue.Equals("true"));
                    else if (paramName.CompareTo("maxInvalidPasswordAttempts") == 0)
                        _maxInvalidPasswordAttempts = Int16.Parse(paramValue);
                    else if (paramName.CompareTo("passwordAttemptWindow") == 0)
                        _passwordAttemptWindow = Int16.Parse(paramValue);
                    else if (paramName.CompareTo("passwordFormat") == 0)
                    {
                        if (paramValue.CompareTo("Clear") == 0)
                            _passwordFormat = MembershipPasswordFormat.Clear;
                        else if (paramValue.CompareTo("Hashed") == 0)
                            _passwordFormat = MembershipPasswordFormat.Hashed;
                        else if (paramValue.CompareTo("Encrypted") == 0)
                            _passwordFormat = MembershipPasswordFormat.Encrypted;
                        else
                            throw new ProviderException("Invalid passwordFormat value");
                    }
                    else if (paramName.CompareTo("minRequiredNonAlphanumericCharacters") == 0)
                        _minRequiredNonAlphanumericCharacters = Int16.Parse(paramValue);
                    else if (paramName.CompareTo("minRequiredPasswordLength") == 0)
                        _minRequiredPasswordLength = Int16.Parse(paramValue);
                    else if (paramName.CompareTo("passwordStrengthRegularExpression") == 0)
                        _passwordStrengthRegularExpression = paramValue;
                }
            }
        }

        public override bool ChangePassword(string username, string oldPwd, string newPwd)
        {
            // Quering user information
            MembershipUser user = GetUser(username, false);
            
            // Validating old password
            if (!ValidateUser(username, oldPwd))
                return false;

            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "UPDATE users SET password=:password," +
                                      "lastActivityDate=:lastActivityDate," +
                                      "lastPasswordChangeDate=:lastPasswordChangeDate " +
                                      "WHERE username=:username";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter("username", DbType.String));
                command.Parameters.Add(new NpgsqlParameter("password", DbType.String));
                command.Parameters.Add(new NpgsqlParameter("lastActivityDate", DbType.DateTime));
                command.Parameters.Add(new NpgsqlParameter("lastPasswordChangeDate", DbType.DateTime));

                command.Parameters["username"].Value = username;
                command.Parameters["password"].Value = EncodePassword(newPwd);
                command.Parameters["lastActivityDate"].Value = "now";
                command.Parameters["lastPasswordChangeDate"].Value = "now";

                command.ExecuteNonQuery ();
            }

            return true;
        }

        public override bool ChangePasswordQuestionAndAnswer(string username,
                      string password,
                      string newPwdQuestion,
                      string newPwdAnswer)
        {
            // Quering user information
            MembershipUser user = GetUser(username, false);

            // Validating old password
            if (!ValidateUser(username, password))
                return false;

            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "UPDATE users SET passwordQuestion=:passwordQuestion," +
                                      "passwordAnswer=:passwordAnswer" +
                                      " WHERE username=:username";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter("username", DbType.String));
                command.Parameters.Add(new NpgsqlParameter("passwordQuestion", DbType.String));
                command.Parameters.Add(new NpgsqlParameter("passwordAnswer", DbType.String));

                command.Parameters["username"].Value = username;
                command.Parameters["passwordQuestion"].Value = newPwdQuestion;
                command.Parameters["passwordAnswer"].Value = EncodePassword(newPwdAnswer);

                command.ExecuteNonQuery();
            }

            return true;
        }

        public override MembershipUser CreateUser(string username,
                 string password,
                 string email,
                 string passwordQuestion,
                 string passwordAnswer,
                 bool isApproved,
                 object providerUserKey,
                 out MembershipCreateStatus status)
        {
            MembershipUser user = null;
            DateTime creationDate = DateTime.Now;

            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "INSERT INTO users VALUES (:username,"+
                                      ":password,:email,:passwordQuestion,"+
                                      ":comment,:isApproved,:isLockedOut,:passwordAnswer,"+
                                      ":failedPasswordAttempWindowStart,"+
                                      ":failedPasswordAttempCount,"+
                                      ":creationDate,"+
                                      ":lastLoginDate,:lastActivityDate,:lastPasswordChangeDate,"+
                                      ":lastLockoutDate)";

                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter("username", DbType.String));
                command.Parameters.Add(new NpgsqlParameter("password", DbType.StringFixedLength));
                command.Parameters.Add(new NpgsqlParameter("email", DbType.StringFixedLength));
                command.Parameters.Add(new NpgsqlParameter("passwordQuestion", DbType.String));
                command.Parameters.Add(new NpgsqlParameter("comment", DbType.String));
                command.Parameters.Add(new NpgsqlParameter("isApproved", DbType.Boolean));
                command.Parameters.Add(new NpgsqlParameter("isLockedOut", DbType.Boolean));
                command.Parameters.Add(new NpgsqlParameter("passwordAnswer", DbType.String));
                command.Parameters.Add(new NpgsqlParameter("failedPasswordAttempWindowStart", DbType.DateTime));
                command.Parameters.Add(new NpgsqlParameter("failedPasswordAttempCount", DbType.Int16));
                command.Parameters.Add(new NpgsqlParameter("creationDate", DbType.DateTime));
                command.Parameters.Add(new NpgsqlParameter("lastLoginDate", DbType.DateTime));
                command.Parameters.Add(new NpgsqlParameter("lastActivityDate", DbType.DateTime));
                command.Parameters.Add(new NpgsqlParameter("lastPasswordChangeDate", DbType.DateTime));
                command.Parameters.Add(new NpgsqlParameter("lastLockoutDate", DbType.DateTime));

                command.Parameters["username"].Value = username;
                command.Parameters["password"].Value = EncodePassword(password);
                command.Parameters["email"].Value = email;
                command.Parameters["passwordQuestion"].Value = passwordQuestion;
                command.Parameters["comment"].Value = "";
                command.Parameters["isApproved"].Value = false;
                command.Parameters["isLockedOut"].Value = false;
                command.Parameters["passwordAnswer"].Value = EncodePassword(passwordAnswer);
                command.Parameters["failedPasswordAttempWindowStart"].Value = DateTime.MinValue;
                command.Parameters["failedPasswordAttempCount"].Value = 0;
                command.Parameters["creationDate"].Value = "now";
                command.Parameters["lastLoginDate"].Value = "now";
                command.Parameters["lastActivityDate"].Value = "now";
                command.Parameters["lastPasswordChangeDate"].Value = "now";
                command.Parameters["lastLockoutDate"].Value = "now";

                try
                {
                    command.ExecuteNonQuery();
                }
                catch (NpgsqlException exc)
                {
                    // Switch according to PostreSQL error codes
                    switch (exc.Code)
                    {
                        case "23505":
                            status = MembershipCreateStatus.DuplicateUserName;
                            break;
                        default:
                            throw;
                    }
                    return null;
                }

                user = GetUser(username, false);
            }
            status = MembershipCreateStatus.Success;
            return user;
        }

        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "DELETE FROM users WHERE username=:username";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter("username", DbType.String));
                command.Parameters["username"].Value = username;

                int rowsAffected = command.ExecuteNonQuery();
                if (rowsAffected != 1)
                    return false;
            }

            return true;
        }

        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "SELECT * FROM users LIMIT :pageSize OFFSET :recordsOffset";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":pageSize", DbType.Int32));
                command.Parameters.Add(new NpgsqlParameter(":recordsOffset", DbType.Int32));

                command.Parameters["pageSize"].Value = pageSize;
                command.Parameters["recordsOffset"].Value = pageSize * pageIndex;

                return GetUsersByQuery(command, out totalRecords);
            }
        }

        public override int GetNumberOfUsersOnline()
        {
            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "SELECT COUNT(username) FROM users WHERE \"lastActivityDate\" >= now()-interval '10 minutes'";
                command.CommandType = CommandType.Text;

                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Int64 numberOfUsersOnline = reader.GetInt64(0);
                        if (numberOfUsersOnline > int.MaxValue)
                            throw new OverflowException("Users amount is out of range");

                        return (int)numberOfUsersOnline;
                    }
                }
            }
            return 0;
        }

        public override string GetPassword(string username, string answer)
        {
            throw new ProviderException("Not implemented yet");
        }

        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            MembershipUser user = null;

            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "SELECT * FROM users WHERE username=:username";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":username", DbType.String));

                command.Parameters["username"].Value = username;

                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (!reader.Read())
                        return null;

                    user = GetUserFromReader(reader);
                }

                // According to flag - update lastUserActivityTime
                if (userIsOnline)
                {
                    command.CommandText = "UPDATE users SET \"lastActivityDate\"=:lastActivityDate WHERE username=:username";
                    command.CommandType = CommandType.Text;

                    command.Parameters.Add(new NpgsqlParameter(":lastActivityDate", DbType.DateTime));
                    command.Parameters.Add(new NpgsqlParameter(":username", DbType.String));

                    command.Parameters["username"].Value = username;
                    command.Parameters["lastActivityDate"].Value = "now";

                    if (command.ExecuteNonQuery() != 1)
                        throw new ProviderException("Failed to update last activity date");
                }
            }
            return user;
        }

        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            return GetUser(providerUserKey as string, userIsOnline);
        }

        public override bool UnlockUser(string username)
        {
            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "UPDATE users SET \"isLockedOut\"=:isLockedOut WHERE username=:username";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter("username", DbType.String));
                command.Parameters.Add(new NpgsqlParameter("isLockedOut", DbType.Boolean));

                command.Parameters["username"].Value = username;
                command.Parameters["isLockedOut"].Value = false;

                return (command.ExecuteNonQuery() == 1);
            }
        }

        public override string GetUserNameByEmail(string email)
        {
            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();
                DbCommand command = databaseConnection.CreateCommand();

                command.CommandText = command.CommandText = "SELECT username FROM users WHERE email=:email";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":email", DbType.String));
                command.Parameters["email"].Value = email;

                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (!reader.Read())
                        return null;
                    return reader.GetString(0);
                }
            }
        }

        public override string ResetPassword(string username, string answer)
        {
            if (!EnablePasswordReset)
                throw new NotSupportedException("Password reset is not supported");

            if (!RequiresQuestionAndAnswer)
                throw new NotSupportedException("Question and answer feature is not enabled");

            if (answer == null)
                throw new ArgumentNullException("Answer can't be null");

            // Generating new password
            string newPassword = Membership.GeneratePassword(Math.Max(MinRequiredPasswordLength, 6),
                MinRequiredNonAlphanumericCharacters);

            using (DbConnection databaseConnection = _databaseFactory.CreateConnection())
            {
                databaseConnection.ConnectionString = _databaseConnectionString;
                databaseConnection.Open();

                // get current answer
                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "SELECT \"passwordAnswer\" FROM users WHERE username=:username";

                command.Parameters.Add(new NpgsqlParameter("username", DbType.String));
                command.Parameters["username"].Value = username;

                string validAnswer = null;
                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (!reader.Read())
                        throw new ProviderException("User not found");
                    
                    if (reader.GetString (0).Equals(EncodePassword(answer)))
                        throw new MembershipPasswordException ();
                }

                command.CommandText = "UPDATE users SET password=:password,"+
                                    "\"lastPasswordChangeDate\"=:lastPasswordChangeDate "+
                                    "WHERE username=:username";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter("username", DbType.String));
                command.Parameters.Add(new NpgsqlParameter("password", DbType.String));
                command.Parameters.Add(new NpgsqlParameter("lastPasswordChangeDate", DbType.DateTime));

                command.Parameters["username"].Value = username;
                command.Parameters["password"].Value = Membership.GeneratePassword(
                    Math.Max(MinRequiredPasswordLength, 6),MinRequiredNonAlphanumericCharacters);

                command.Parameters["lastPasswordChangeDate"].Value = "now";

                if (command.ExecuteNonQuery() != 1)
                    throw new ProviderException("Failed to update password");

                return newPassword;
            }
        }

        public override void UpdateUser(MembershipUser user)
        {
            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "UPDATE users SET email=:email,comment=:comment,isApproved=:isApproved,lastLoginDate=:lastLoginDate,lastActivityDate=:lastActivityDate WHERE username=:username";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":username", DbType.String));
                command.Parameters.Add(new NpgsqlParameter(":email", DbType.String));
                command.Parameters.Add(new NpgsqlParameter(":comment", DbType.String));
                command.Parameters.Add(new NpgsqlParameter(":isApproved", DbType.Boolean));
                command.Parameters.Add(new NpgsqlParameter(":lastLoginDate", DbType.DateTime));
                command.Parameters.Add(new NpgsqlParameter(":lastActivityDate", DbType.DateTime));

                command.Parameters["username"].Value = user.UserName;
                command.Parameters["email"].Value = user.Email;
                command.Parameters["comment"].Value = user.Comment;
                command.Parameters["isApproved"].Value = user.IsApproved;
                command.Parameters["lastLoginDate"].Value = user.LastLoginDate;
                command.Parameters["lastActivityDate"].Value = user.LastActivityDate;

                command.ExecuteNonQuery();
            }
        }

        public override bool ValidateUser(string username, string password)
        {
            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "SELECT \"password\" AS password,\"isLockedOut\" AS isLockedOut FROM users WHERE username=:username";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":username", DbType.String));

                command.Parameters["username"].Value = username;

                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (!reader.Read())
                        throw new ProviderException("User not found");

                    int passwordOrdinal = reader.GetOrdinal("password");
                    int isLockedOutOrdinal = reader.GetOrdinal("isLockedOut");

                    if (reader.GetBoolean(isLockedOutOrdinal))
                        throw new ProviderException("User account is locked");

                    string userPassword = reader.GetString(passwordOrdinal);
                    string encodedPassword = EncodePassword(password);

                    if (!reader.GetString(passwordOrdinal).Equals(EncodePassword(password)))
                    {
                        UpdateFailureCount(username);
                        throw new ProviderException("Failed to update user details");
                    }
                }

                // Updating related user information
                command.CommandText = "UPDATE users SET \"lastLoginDate\"=:lastLoginDate,\"failedPasswordAttempCount\"=:failedPasswordAttempCount WHERE username=:username";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter("lastLoginDate", DbType.DateTime));
                command.Parameters.Add(new NpgsqlParameter("failedPasswordAttempCount", DbType.Int16));
                command.Parameters.Add(new NpgsqlParameter("username", DbType.String));

                command.Parameters["lastLoginDate"].Value = "now";
                command.Parameters["failedPasswordAttempCount"].Value = 0;
                command.Parameters["username"].Value = username;

                command.ExecuteNonQuery();
            }
            return true; 
        }

        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();
                DbCommand command = databaseConnection.CreateCommand();

                command.CommandText = command.CommandText = "SELECT * FROM users WHERE username LIKE :usernameToMatch LIMIT :pageSize OFFSET :recordsOffset";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":usernameToMatch", DbType.String));
                command.Parameters.Add(new NpgsqlParameter(":pageSize", DbType.Int32));
                command.Parameters.Add(new NpgsqlParameter(":recordsOffset", DbType.Int32));

                command.Parameters["usernameToMatch"].Value = "%"+usernameToMatch+"%";
                command.Parameters["pageSize"].Value = pageSize;
                command.Parameters["recordsOffset"].Value = pageSize * pageIndex;

                return GetUsersByQuery(command, out totalRecords);
            }
        }

        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();
                DbCommand command = databaseConnection.CreateCommand();

                command.CommandText = command.CommandText = "SELECT * FROM users WHERE email LIKE :emailToMatch LIMIT :pageSize OFFSET :recordsOffset";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter(":emailToMatch", DbType.String));
                command.Parameters.Add(new NpgsqlParameter(":pageSize", DbType.Int32));
                command.Parameters.Add(new NpgsqlParameter(":recordsOffset", DbType.Int32));

                command.Parameters["emailToMatch"].Value = "%"+emailToMatch+"%";
                command.Parameters["pageSize"].Value = pageSize;
                command.Parameters["recordsOffset"].Value = pageSize * pageIndex;

                return GetUsersByQuery(command, out totalRecords);
            }
        }

        private void UpdateFailureCount(string username)
        {
            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            using (databaseConnection)
            {
                databaseConnection.Open();

                DbCommand command = databaseConnection.CreateCommand();
                command.CommandText = "SELECT \"failedPasswordAttempWindowStart\" AS failedPasswordAttempWindowStart,"+
                    "\"failedPasswordAttempCount\" AS failedPasswordAttempCount FROM users WHERE username=:username";
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter("username", DbType.String));

                command.Parameters["username"].Value = username;

                // Values to fill up from DB
                DateTime windowEnd = DateTime.MinValue;
                int failureCount = 1;
                bool isLockedOut = false;

                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (!reader.Read())
                        throw new ProviderException("Failed to locate specified user");

                    int failedPwdAttempWinStartOrdinal = reader.GetOrdinal("failedPasswordAttempWindowStart");
                    int failedPasswordAttempCountOrdinal = reader.GetOrdinal("failedPasswordAttempCount");

                    // Filling up values
                    windowEnd = reader.GetDateTime(failedPwdAttempWinStartOrdinal).AddMinutes(PasswordAttemptWindow);
                    failureCount = reader.GetInt16(failedPasswordAttempCountOrdinal) + 1;
                    isLockedOut = (failureCount > MaxInvalidPasswordAttempts);
                }

                StringBuilder commandText = new StringBuilder();
                commandText.Append ("UPDATE users SET \"failedPasswordAttempCount\"=:failedAttempCount");

                // If this is a first fail - start window counter
                if (failureCount == 1)
                    commandText.Append(",\"failedPasswordAttempWindowStart\"='now'");

                if (isLockedOut)
                    commandText.Append(",\"isLockedOut\"=TRUE,\"lastLockoutDate\"='now'");

                commandText.Append(" WHERE username=:username");

                command.CommandText = commandText.ToString();
                command.CommandType = CommandType.Text;

                command.Parameters.Add(new NpgsqlParameter ("failedAttempCount", DbType.Int16));
                command.Parameters.Add(new NpgsqlParameter ("username", DbType.String));

                command.Parameters["failedAttempCount"].Value = failureCount;
                command.Parameters["username"].Value = username;

                if (command.ExecuteNonQuery() != 1)
                    throw new ProviderException("Failed to update user record");
            }
        }

        private MembershipUserCollection GetUsersByQuery(DbCommand queryCommand, out Int32 totalRecords)
        {
            DbConnection databaseConnection = _databaseFactory.CreateConnection();
            databaseConnection.ConnectionString = _databaseConnectionString;

            MembershipUserCollection users = new MembershipUserCollection();
            totalRecords = 0;

            using (DbDataReader reader = queryCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    MembershipUser user = GetUserFromReader(reader);
                    users.Add(user);
                    totalRecords++;
                }
            }

            return users;
        }

        //
        // GetUserFromReader
        //   Creates MembershipUser by positioned DbDataReader
        private MembershipUser GetUserFromReader(DbDataReader reader)
        {
            int loginOrdinal = reader.GetOrdinal("username");
            int emailOrdinal = reader.GetOrdinal("email");
            int passwordQuestionOrdinal = reader.GetOrdinal("passwordQuestion");
            int commentOrdinal = reader.GetOrdinal("comment");
            int isApprovedOrdinal = reader.GetOrdinal("isApproved");
            int isLockedOutOrdinal = reader.GetOrdinal("isLockedOut");
            int creationDateOrdinal = reader.GetOrdinal("creationDate");
            int lastLoginDateOrdinal = reader.GetOrdinal("lastLoginDate");
            int lastActivityDateOrdinal = reader.GetOrdinal("lastActivityDate");
            int lastPasswordChangeDateOrdinal = reader.GetOrdinal("lastPasswordChangeDate");
            int lastLockoutDateOrdinal = reader.GetOrdinal("lastLockoutDate");

            MembershipUser user = new MembershipUser(
                this.Name,
                reader.GetString(loginOrdinal),
                reader.GetString(loginOrdinal),
                reader.GetString(emailOrdinal),
                reader.GetString(passwordQuestionOrdinal),
                reader.GetString(commentOrdinal),
                reader.GetBoolean(isApprovedOrdinal),
                reader.GetBoolean(isLockedOutOrdinal),
                reader.GetDateTime(creationDateOrdinal),
                reader.GetDateTime(lastLoginDateOrdinal),
                reader.GetDateTime(lastActivityDateOrdinal),
                reader.GetDateTime(lastPasswordChangeDateOrdinal),
                reader.GetDateTime(lastLoginDateOrdinal));

            return user;
        }

        //
        // EncodePassword
        //   Encodes password according to PasswordFormat attribute
        private string EncodePassword(string password)
        {
            string encodedPassword = password;

            switch (PasswordFormat)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    throw new ProviderException("Encrypted passwords are not supported");
                case MembershipPasswordFormat.Hashed:
                    System.Security.Cryptography.HMACSHA1 hash = new System.Security.Cryptography.HMACSHA1();
                    hash.Key = _passwordHashKey;
                    encodedPassword =
                      Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
                    break;
                default:
                    throw new ProviderException("Unsupported password format.");
            }

            return encodedPassword;
        }

        //
        // HexToByte
        //   Converts a hexadecimal string to a byte array. Used to convert encryption
        //   key values from the configuration.
        //
        private byte[] HexToByte(string hexString)
        {
            byte[] returnBytes = new byte[hexString.Length / 2];
            for (int i = 0; i < returnBytes.Length; i++)
                returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
            return returnBytes;
        }

        // Read/write only attributes
        private string _applicationName;

        // Read only attributes
        private bool _enablePasswordReset;
        private bool _enablePasswordRetreival;
        private bool _requiresQuestionAndAnswer;
        private bool _requiresUniqueEmail;
        private int _maxInvalidPasswordAttempts;
        private int _passwordAttemptWindow;
        private MembershipPasswordFormat _passwordFormat;
        private int _minRequiredNonAlphanumericCharacters;
        private int _minRequiredPasswordLength;
        private string _passwordStrengthRegularExpression;

        // Internal
        private string _databaseFactoryName;
        private string _databaseConnectionString;
        private DbProviderFactory _databaseFactory;
        private byte[] _passwordHashKey;
    }
}